-- @owner: cr13
-- @date: 2025/3/27
-- @testpoint:视图引用自定义函数，引用位置from后，部分合理报错
--step1:创建自定义函数;expect:成功
drop fUNCTION if exists func1;
CREATE FUNCTION func1(n1 IN NUMBER, n2 IN NUMBER)
RETURNS NUMBER AS $$
DECLARE
 sum_result NUMBER;
BEGIN
 sum_result := n1 + n2;
 RETURN sum_result;
END;
$$ LANGUAGE plpgsql;
/
--step2:创建视图;expect:成功
drop view if exists v_ddl0008;
create view v_ddl0008 as select * from func1(1,2);
--step3:查询视图;expect:成功
select * from v_ddl0008;
--step4:删除函数;expect:成成功
drop fUNCTION func1;
--step5:查询视图状态;expect:无效
select valid from pg_object where object_type='v' and object_oid in(select oid from pg_class where relname='v_ddl0008');
--step6:查询视图数据;expect:报错
select * from v_ddl0008;
--step7:查询视图定义;expect:有告警
select pg_get_viewdef('v_ddl0008');

--step8:重新创建函数，和原始函数一样;expect:成功
CREATE FUNCTION func1(n1 IN NUMBER, n2 IN NUMBER)
RETURNS NUMBER AS $$
DECLARE
 sum_result NUMBER;
BEGIN
 sum_result := n1 + n2;
 RETURN sum_result;
END;
$$ LANGUAGE plpgsql;
/
--step9:查询视图状态;expect:无效
select valid from pg_object where object_type='v' and object_oid in(select oid from pg_class where relname='v_ddl0008');
--step10:查询视图定义;expect:成功
select pg_get_viewdef('v_ddl0008');
--step11:查询视图数据;expect:成功
select * from v_ddl0008;
--step12:查询视图定义;expect:成功
select pg_get_viewdef('v_ddl0008');
--step13:查询视图状态;expect:有效
select valid from pg_object where object_type='v' and object_oid in(select oid from pg_class where relname='v_ddl0008');
--step14:清理环境;expect:成功
drop view if exists v_ddl0008;
drop fUNCTION func1;